package billings.classtasks;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {

	//Class table
	public static final String CLASS_TABLE = "class";
	public static final String CLASS_COLUMN_ID = "_id";
	public static final String CLASS_COURSE_NUMBER = "course_number";
	public static final String CLASS_COURSE_MAJOR = "course_major";
	public static final String CLASS_COURSE_PROFESSOR = "course_professor";
	
	//Task table
	public static final String TASK_TABLE = "task";
	public static final String TASK_COLUMN_ID = "_id";
	public static final String TASK_TITLE = "title";
	public static final String TASK_DESCRIPTION = "description";
	public static final String TASK_DUE_DATE = "due_date";
	public static final String CLASS_ID_FK = "class_id_fk";
	
	//Instructor table
	public static final String INSTRUCTOR_TABLE = "instructor";
	public static final String INSTRUCTOR_COLUMN_ID = "_id";
	public static final String INSTRUCTOR_FIRST_NAME = "first_name";
	public static final String INSTRUCTOR_LAST_NAME = "last_name";
	public static final String INSTRUCTOR_DEPARTMENT = "department";
	public static final String INSTRUCTOR_EMAIL = "email";
	public static final String INSTRUCTOR_PHONE_NUMBER = "phone_number";
	public static final String INSTRUCTOR_OFFICE_BUILDING = "office_building";
	public static final String INSTRUCTOR_OFFICE_NUMBER = "office_number";
	public static final String INSTRUCTOR_TYPE_ID_FK = "instructor_type_id_fk";
	
	//Instructor Type table
	public static final String INSTRUCTOR_TYPE_TABLE = "instructor_type";
	public static final String INSTRUCTOR_TYPE_COLUMN_ID = "_id";
	public static final String INSTRUCTOR_TYPE_DESCRIPTION = "description";
	public static final String INSTRUCTOR_TYPE_ABBREVIATION = "abbreviation";
	
	private static final String DATABASE_NAME = "classtasks.db";
	private static final int DATABASE_VERSION = 3;
	
	//create sql
	private static final String DATABASE_CREATE_CLASS = "create table " +
												  		CLASS_TABLE + "(" + CLASS_COLUMN_ID +
												  		" integer primary key autoincrement, " +
												  		CLASS_COURSE_NUMBER + " text not null, " +
												  		CLASS_COURSE_MAJOR + " text not null, " + 
												  		CLASS_COURSE_PROFESSOR + " text); ";
												  
	private static final String DATABASE_CREATE_TASK = " create table " +
												  	   TASK_TABLE + "(" + TASK_COLUMN_ID +
												  	   " integer primary key autoincrement, " +
												  	   TASK_TITLE + " text not null, " +
												  	   TASK_DESCRIPTION + " text, " + 
												  	   TASK_DUE_DATE + " text not null, " + 
												  	   CLASS_ID_FK + " integer not null); ";
	
	private static final String DATABASE_CREATE_INSTRUCTOR = "create table " +
														     INSTRUCTOR_TABLE + "(" + INSTRUCTOR_COLUMN_ID +
														     " integer primary key autoincrement, " +
														     INSTRUCTOR_FIRST_NAME + " text not null, " +
														     INSTRUCTOR_LAST_NAME + " text not null, " +
														     INSTRUCTOR_DEPARTMENT + " text, " +
														     INSTRUCTOR_EMAIL + " text, " +
														     INSTRUCTOR_PHONE_NUMBER + " text, " +
														     INSTRUCTOR_OFFICE_BUILDING + " text, " +
														     INSTRUCTOR_OFFICE_NUMBER + " text, " +
														     INSTRUCTOR_TYPE_ID_FK + " integer not null); ";
	
	private static final String DATABASE_CREATE_INSTRUCTOR_TYPE = "create table " +
																  INSTRUCTOR_TYPE_TABLE + "(" + INSTRUCTOR_TYPE_COLUMN_ID +
																  " integer primary key autoincrement, " +
																  INSTRUCTOR_TYPE_DESCRIPTION + " text not null," +
																  INSTRUCTOR_TYPE_ABBREVIATION + " text not null); ";
	
	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}	
	
	@Override
	public void onCreate(SQLiteDatabase database) {
		database.execSQL("DROP TABLE IF EXISTS " + CLASS_TABLE);
		database.execSQL("DROP TABLE IF EXISTS " + TASK_TABLE);
		database.execSQL("DROP TABLE IF EXISTS " + INSTRUCTOR_TABLE);
		database.execSQL("DROP TABLE IF EXISTS " + INSTRUCTOR_TYPE_TABLE);
		database.execSQL(DATABASE_CREATE_CLASS);
		database.execSQL(DATABASE_CREATE_TASK);
		database.execSQL(DATABASE_CREATE_INSTRUCTOR);
		database.execSQL(DATABASE_CREATE_INSTRUCTOR_TYPE);
	}

	@Override
	public void onOpen(SQLiteDatabase database){
		onCreate(database);
		//database.execSQL("DELETE FROM " + CLASS_TABLE + ";");
		//database.execSQL("DELETE FROM " + TASK_TABLE + ";");
		
		//setup content values
		ContentValues values = new ContentValues();
		values.put(INSTRUCTOR_TYPE_DESCRIPTION, "Professor");
		values.put(INSTRUCTOR_TYPE_ABBREVIATION, "Prof");
		
		database.insert(INSTRUCTOR_TYPE_TABLE, null, values);
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(DatabaseHelper.class.getName(), "Upgrading from version " + oldVersion + " to " + newVersion +
					                              ". This will destroy all current database data.");
			db.execSQL("DROP TABLE IF EXISTS " + CLASS_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + TASK_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + INSTRUCTOR_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + INSTRUCTOR_TYPE_TABLE);
			onCreate(db);
	}

	public void clearAll(SQLiteDatabase db){
		db.execSQL("DELETE FROM " + CLASS_TABLE + ";");
		db.execSQL("DELETE FROM " + TASK_TABLE + ";");
		db.execSQL("DELETE FROM " + INSTRUCTOR_TABLE + ";");
		db.execSQL("DELETE FROM " + INSTRUCTOR_TYPE_TABLE + ";");
	}
}
